﻿using Dapper;
using Entity;
using IRealize;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Realize
{
    public class DDGLD : IDDGLD
    {
        public async Task<int> AddHFDJ(Human_File human_File, string sqlserver)
        {

            using (SqlConnection connection = new SqlConnection(sqlserver))
            {
                await connection.OpenAsync();

              

                // 执行插入操作
                string sql = @"
            INSERT INTO [dbo].[human_file] (
    [human_id], [first_kind_id], [first_kind_name], 
    [second_kind_id], [second_kind_name], [third_kind_id], [third_kind_name],
      [human_major_kind_id], [human_major_kind_name],[human_major_id], [hunma_major_name], [human_pro_designation],
    [human_name], [human_sex], [human_email], [human_telephone], 
    [human_qq], [human_mobilephone], [human_address], [human_postcode], 
    [human_nationality], [human_birthplace], [human_birthday], [human_race],
    [human_religion], [human_party], [human_id_card], [human_society_security_id],
    [human_age], [human_educated_degree], [human_educated_years], 
    [human_educated_major], [salary_standard_name], [human_bank], [human_account],
    [register], [regist_time], [human_speciality], [human_hobby], 
    [remark], [human_histroy_records], [human_family_membership],[human_picture]
)
VALUES (
    @human_id, @first_kind_id, @first_kind_name, 
    @second_kind_id, @second_kind_name, @third_kind_id, @third_kind_name,
    @human_major_kind_id,@human_major_kind_name,@human_major_id,@hunma_major_name, @human_pro_designation,
    @human_name, @human_sex, @human_email, @human_telephone, 
    @human_qq, @human_mobilephone, @human_address, @human_postcode, 
    @human_nationality, @human_birthplace, @human_birthday, @human_race,
    @human_religion, @human_party, @human_id_card, @human_society_security_id,
    @human_age, @human_educated_degree, @human_educated_years, 
    @human_educated_major, @salary_standard_name, @human_bank, @human_account,
    @register, @regist_time, @human_speciality, @human_hobby, 
    @remark, @human_histroy_records, @human_family_membership,@human_picture
)";



                // 使用动态参数对象，确保属性名正确
                var parameters = new
                {
                    human_File.human_id,
                    human_File.first_kind_id,
                    human_File.first_kind_name,
                    human_File.second_kind_id,
                    human_File.second_kind_name,
                    human_File.third_kind_id,
                    human_File.third_kind_name,
                    human_File.human_major_kind_id,
                    human_File.human_major_kind_name,
                    human_File.human_major_id,
                    human_File.hunma_major_name,
                    human_File.human_pro_designation,
                    human_File.human_name,
                    human_File.human_sex,
                    human_File.human_email,
                    human_File.human_telephone,
                    human_File.human_qq,
                    human_File.human_mobilephone,
                    human_File.human_address,
                    human_File.human_postcode,
                    human_File.human_nationality,
                    human_File.human_birthplace,
                    human_File.human_birthday,
                    human_File.human_race,
                    human_File.human_religion,
                    human_File.human_party,
                    human_File.human_id_card,
                    human_File.human_society_security_id,
                    human_File.human_age,
                    human_File.human_educated_degree,
                    human_File.human_educated_years,
                    human_File.human_educated_major,
                    human_File.salary_standard_name,
                    human_File.human_bank,
                    human_File.human_account,
                    human_File.register,
                    human_File.regist_time,
                    human_File.human_speciality,
                    human_File.human_hobby,
                    human_File.remark,
                    human_File.human_histroy_records,
                    human_File.human_family_membership,
                    human_File.human_picture,

                };


                return await connection.ExecuteAsync(sql, parameters);



            }


        }

        public async Task<int> AddMCTJ(Major_Change major_Change, string sqlserver)
        {

            using (SqlConnection sqlConnection = new SqlConnection(sqlserver))
            {
                // 构建插入语句，使用参数化查询防止SQL注入
                string sql = @"
            INSERT INTO [dbo].[major_change] (
                [first_kind_id], [first_kind_name], [second_kind_id], [second_kind_name],
                [third_kind_id], [third_kind_name], [major_kind_id], [major_kind_name],
                [major_id], [major_name], [new_first_kind_id], [new_first_kind_name],
                [new_second_kind_id], [new_second_kind_name], [new_third_kind_id], [new_third_kind_name],
                [new_major_kind_id], [new_major_kind_name], [new_major_id], [new_major_name],
                [human_id], [human_name], [salary_standard_id], [salary_standard_name],
                [salary_sum], [new_salary_standard_id], [new_salary_standard_name], [new_salary_sum],
                [change_reason], [check_reason], [check_status], [register], [checker],
                [regist_time], [check_time]
            ) VALUES (
                @first_kind_id, @first_kind_name, @second_kind_id, @second_kind_name,
                @third_kind_id, @third_kind_name, @major_kind_id, @major_kind_name,
                @major_id, @major_name, @new_first_kind_id, @new_first_kind_name,
                @new_second_kind_id, @new_second_kind_name, @new_third_kind_id, @new_third_kind_name,
                @new_major_kind_id, @new_major_kind_name, @new_major_id, @new_major_name,
                @human_id, @human_name, @salary_standard_id, @salary_standard_name,
                @salary_sum, @new_salary_standard_id, @new_salary_standard_name, @new_salary_sum,
                @change_reason, @check_reason, @check_status, @register, @checker,
                @regist_time, @check_time
            )";

                // 构建参数对象，所有属性需与Major_Change类匹配
                var parameters = new
                {
                    major_Change.first_kind_id,
                    major_Change.first_kind_name,
                    major_Change.second_kind_id,
                    major_Change.second_kind_name,
                    major_Change.third_kind_id,
                    major_Change.third_kind_name,
                    major_Change.major_kind_id,
                    major_Change.major_kind_name,
                    major_Change.major_id,
                    major_Change.major_name,
                    major_Change.new_first_kind_id,
                    major_Change.new_first_kind_name,
                    major_Change.new_second_kind_id,
                    major_Change.new_second_kind_name,
                    major_Change.new_third_kind_id,
                    major_Change.new_third_kind_name,
                    major_Change.new_major_kind_id,
                    major_Change.new_major_kind_name,
                    major_Change.new_major_id,
                    major_Change.new_major_name,
                    major_Change.human_id,
                    major_Change.human_name,
                    major_Change.salary_standard_id,
                    major_Change.salary_standard_name,
                    major_Change.salary_sum,
                    major_Change.new_salary_standard_id,
                    major_Change.new_salary_standard_name,
                    major_Change.new_salary_sum,
                    major_Change.change_reason,
                    major_Change.check_reason,
                    major_Change.check_status,
                    major_Change.register,
                    major_Change.checker,
                    major_Change.regist_time,    
                    major_Change.check_time     
                };

                // 执行插入并返回受影响的行数
                return await sqlConnection.ExecuteAsync(sql, parameters);
            }

        }

        public async Task<PagedResult<Human_File>> CXHumanFiles(string sqlserver, string firstKindName, string secondKindName, string thirdKindName, DateTime? startDate, DateTime? endDate, int pageNumber = 1, int pageSize = 1)
        {
            // 使用OFFSET/FETCH实现分页
            string sql = @"
SELECT * 
FROM (
    SELECT hfd.*,
        ROW_NUMBER() OVER (ORDER BY hfd.human_id) AS RowNum
    FROM human_file hfd
    WHERE 1=1
    AND (@FirstKindName IS NULL OR hfd.first_kind_name = @FirstKindName)
    AND (@SecondKindName IS NULL OR hfd.second_kind_name = @SecondKindName)
    AND (@ThirdKindName IS NULL OR hfd.third_kind_name = @ThirdKindName)
    AND (@StartDate IS NULL OR CAST(hfd.regist_time AS DATE) >= @StartDate)
    AND (@EndDate IS NULL OR CAST(hfd.check_time AS DATE) <= @EndDate)
) AS SubQuery
WHERE RowNum BETWEEN (@PageNumber - 1) * @PageSize + 1 AND @PageNumber * @PageSize
ORDER BY RowNum";

            string countSql = @"
SELECT COUNT(*) 
FROM human_file hfd
WHERE 1=1
AND (@FirstKindName IS NULL OR hfd.first_kind_name = @FirstKindName)
AND (@SecondKindName IS NULL OR hfd.second_kind_name = @SecondKindName)
AND (@ThirdKindName IS NULL OR hfd.third_kind_name = @ThirdKindName)
AND (@StartDate IS NULL OR CAST(hfd.regist_time AS DATE) >= @StartDate)
AND (@EndDate IS NULL OR CAST(hfd.check_time AS DATE) <= @EndDate)";


            using (var connection = new SqlConnection(sqlserver))
            {
                await connection.OpenAsync();

                var parameters = new DynamicParameters();
                //parameters.Add("@FirstKindName", firstKindName);
                //parameters.Add("@SecondKindName", secondKindName);
                //parameters.Add("@ThirdKindName", thirdKindName);

                parameters.Add("@FirstKindName", string.IsNullOrEmpty(firstKindName) ? null : firstKindName);
                parameters.Add("@SecondKindName", string.IsNullOrEmpty(secondKindName) ? null : secondKindName);
                parameters.Add("@ThirdKindName", string.IsNullOrEmpty(thirdKindName) ? null : thirdKindName);
                parameters.Add("@StartDate", startDate);
                parameters.Add("@EndDate", endDate);
                parameters.Add("@PageNumber", pageNumber);
                parameters.Add("@PageSize", pageSize);

                // 执行分页查询
                var items = await connection.QueryAsync<Human_File>(sql, parameters);

                // 获取总记录数
                var totalCount = await connection.ExecuteScalarAsync<int>(countSql, parameters);

                return new PagedResult<Human_File>
                {
                    Items =items.ToList(),
                    TotalCount = totalCount,
                    PageNumber = pageNumber,
                    PageSize = pageSize
                };
            }
        }

        public async Task<PagedResult<Major_Change>> CXMajor_name(string sqlserver, string firstKindName, string secondKindName, string thirdKindName, string jobCategoryName, string jobName, DateTime? startDate, DateTime? endDate, int pageNumber = 1, int pageSize = 1)
        {


            // 构建动态SQL查询
            string sql = @"
            SELECT * 
            FROM (
                SELECT *,
                    ROW_NUMBER() OVER (ORDER BY human_id) AS RowNum
                FROM major_change hf
                WHERE 1=1
                AND (@FirstKindName IS NULL OR hf.first_kind_name = @FirstKindName)
                AND (@SecondKindName IS NULL OR hf.second_kind_name = @SecondKindName)
                AND (@ThirdKindName IS NULL OR hf.third_kind_name = @ThirdKindName)
                AND (@JobCategoryName IS NULL OR hf.major_kind_name = @JobCategoryName)
                AND (@JobName IS NULL OR hf.major_name = @JobName) -- 修正字段名
                AND (@StartDate IS NULL OR hf.regist_time >= @StartDate)
                AND (@EndDate IS NULL OR hf.check_time <= @EndDate)
            ) AS SubQuery
            WHERE RowNum BETWEEN (@PageNumber - 1) * @PageSize + 1 AND @PageNumber * @PageSize
            ORDER BY RowNum";
            
                        string countSql = @"
            SELECT COUNT(*) 
            FROM major_change hf
            WHERE 1=1
            AND (@FirstKindName IS NULL OR hf.first_kind_name = @FirstKindName)
            AND (@SecondKindName IS NULL OR hf.second_kind_name = @SecondKindName)
            AND (@ThirdKindName IS NULL OR hf.third_kind_name = @ThirdKindName)
            AND (@JobCategoryName IS NULL OR hf.major_kind_name = @JobCategoryName)
            AND (@JobName IS NULL OR hf.major_name = @JobName) -- 修正字段名
            AND (@StartDate IS NULL OR hf.regist_time >= @StartDate)
            AND (@EndDate IS NULL OR hf.check_time <= @EndDate)";

            using (var connection = new SqlConnection(sqlserver))
            {
                await connection.OpenAsync();

                var parameters = new DynamicParameters();
                parameters.Add("@FirstKindName", string.IsNullOrEmpty(firstKindName) ? null : firstKindName);
                parameters.Add("@SecondKindName", string.IsNullOrEmpty(secondKindName) ? null : secondKindName);
                parameters.Add("@ThirdKindName", string.IsNullOrEmpty(thirdKindName) ? null : thirdKindName);
                parameters.Add("@JobCategoryName", string.IsNullOrEmpty(jobCategoryName) ? null : jobCategoryName);
                parameters.Add("@JobName", string.IsNullOrEmpty(jobName) ? null : jobName);
                parameters.Add("@StartDate", startDate);
                parameters.Add("@EndDate", endDate);
                parameters.Add("@PageNumber", pageNumber);
                parameters.Add("@PageSize", pageSize);

                // 执行分页查询
                var items = await connection.QueryAsync<Major_Change>(sql, parameters);

                // 获取总记录数
                var totalCount = await connection.ExecuteScalarAsync<int>(countSql, parameters);

                return new PagedResult<Major_Change>
                {
                    Items = items.ToList(),
                    TotalCount = totalCount,
                    PageNumber = pageNumber,
                    PageSize = pageSize
                };
            }


        }

        public async Task<Human_File_Dig?> SelectHFDIDAsync(int hfd_id, string sqlserver)
        {
            Task<Human_File_Dig?> task = Task<Human_File_Dig>.Run(() =>
            {
                using (SqlConnection sqlConnection = new SqlConnection(sqlserver))
                {

                    string sql = $@"select * from [dbo].[human_file_dig] where [hfd_id]='{hfd_id}' ";

                    return sqlConnection.Query<Human_File_Dig>(sql).FirstOrDefault();
                }

            });

            return await task;
        }

        public async Task<Major_Change?> SelectHuFiAsync(int mch_id, string sqlserver)
        {
            Task<Major_Change?> task = Task<Major_Change>.Run(() =>
            {
                using (SqlConnection sqlConnection = new SqlConnection(sqlserver))
                {

                    string sql = $@" select * from [dbo].[major_change] where [mch_id]='{mch_id}' ";

                    return sqlConnection.Query<Major_Change>(sql).FirstOrDefault();
                }

            });

            return await task;
        }

        public async Task<int> UpdateMCBTG(Major_Change major_Change, string sqlserver)
        {
            using (SqlConnection sqlConnection = new SqlConnection(sqlserver))
            {

                string sql = $@" update [dbo].[major_change] set [check_status]='{major_Change.check_status}',[checker]='{major_Change.checker}',[check_time]='{major_Change.check_time}' where [mch_id]='{major_Change.mch_id}' ";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        public async Task<int> UpdateMCTG(Major_Change major_Change, string sqlserver)
        {
            using (SqlConnection sqlConnection = new SqlConnection(sqlserver))
            {

                string sql = $@" update [dbo].[major_change] set [check_reason]='{major_Change.check_reason}' ,[check_status]='{major_Change.check_status}',[checker]='{major_Change.checker}',[check_time]='{major_Change.check_time}' where [mch_id]='{major_Change.mch_id}'  ";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        public async Task<List<Human_File_Dig>> XCXLK(string sqlserver)
        {
            using (var sqlConnection = new SqlConnection(sqlserver))
            {
                await sqlConnection.OpenAsync(); // 异步打开连接

                string sql = "SELECT TOP 2 * FROM [dbo].[human_file_dig] ";

                try
                {
                    // 使用异步查询方法
                    var result = await sqlConnection.QueryAsync<Human_File_Dig>(sql);
                    return result.AsList(); // 转换为List
                }
                catch (SqlException ex)
                {
                    // 处理SQL异常（如连接失败、查询错误）
                    Console.WriteLine($"数据库错误：{ex.Message}");
                    throw; // 可选择重新抛出或包装异常
                }
                catch (Exception ex)
                {
                    // 处理其他异常
                    Console.WriteLine($"意外错误：{ex.Message}");
                    throw;
                }
            }
        }



    }
}
